package com.elitel.frame.business.service.impl;

import com.alibaba.fastjson.JSON;
import com.elitel.common.businessJdbc.BusinessScriptPreparedStatement;
import com.elitel.common.businessJdbc.BusinessScriptPreparedStatementHandler;
import com.elitel.common.exception.BaseException;
import com.elitel.common.mongodb.MongoDBUtil;
import com.elitel.common.mongodb.QueryInfo;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.base.component.CacheUtil;
import com.elitel.frame.base.component.RedisCacheMultipleTable;
import com.elitel.frame.base.component.RedisKey;
import com.elitel.frame.base.service.CfgExtsqlService;
import com.elitel.frame.base.service.strategy.sql.OgnlParseSQLStrategy;
import com.elitel.frame.business.dao.ext.ExecuteSqlMapper;
import com.elitel.frame.business.dao.ext.MultipleTableServerMapper;
import com.elitel.frame.business.service.MultipleTableServerService;
import com.elitel.frame.business.service.access.PrepareFilterSqlHandler;
import com.elitel.frame.business.service.common.config.XMLMapperBuilderHelper;
import com.elitel.frame.core.util.ApplicationContextHelper;
import com.elitel.frame.main.dao.BaseSqlinforMapper;
import com.elitel.frame.main.dao.ext.ExtBaseSqlconfigMapper;
import com.elitel.frame.main.dao.ext.ExtSysExceptionMapper;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.access.FilterSqlHandlerDTO;
import com.elitel.frame.main.entity.ext.QueryFiled;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.*;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Service
public class MultipleTableServerServiceImpl implements MultipleTableServerService {
    private static final Logger logger =
            LoggerFactory.getLogger(MultipleTableServerServiceImpl.class);

    @Autowired
    private MultipleTableServerMapper multipleTableServerMapper;
    @Autowired
    private ExtBaseSqlconfigMapper extBaseSqlconfigMapper;
    @Autowired
    @Qualifier("slaveDataSource")
    DataSource dataSource;

    @Autowired
    private RedisCacheMultipleTable redisCachMultipleTable;
//  @Autowired private RedisCacheService redisCacheService;

    @Autowired
    private RedisKey redisKey;

    @Resource
    private BaseSqlinforMapper baseSqlinforMapper;
    @Resource
    private ExtSysExceptionMapper extSysExceptionMapper;
    @Autowired
    private CacheUtil cacheUtil;
    @Autowired
    private CacheServiceData cacheServiceData;

    @Autowired
    private CfgExtsqlService cfgExtSqlService;

    @Value("${cache.dataredis.library:#{2}}")
    private int rdi;

    @Value("${spring.jackson.date-format}")
    private String dateformat;

    @Resource
    private OgnlParseSQLStrategy ognlParseSQLStrategy;
    @Resource
    private ExecuteSqlMapper executeSqlMapper;

    @Resource(name = "salveSqlSessionFactory")
    private SqlSessionFactory sqlSessionFactory;
    @Autowired
    private BusinessScriptPreparedStatementHandler businessScriptPreparedStatementHandler;

    private String rediscount = "_count";
    private Map<String, Object> maplock = new ConcurrentHashMap<>();

    private volatile boolean state = true;
    private volatile boolean state1 = true;

    private final Map<String, XMLMapperBuilderHelper> serviceConcurrentMap = new ConcurrentHashMap<>();


    private Object lock = new Object();

    @Override
    public PageResponseVo<Map<String, Object>> multipleTableServerQueryPagination(
            CfgDb cfgDB,
            String pageNO,
            String pageSize,
            Map<String, Object> passConditionMap,
            //String conQuery,
            CfgExtsql cfgExtsql,
            String refCache)
            throws Exception {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();

        if (cfgExtsql.getSyscatch().equals(1)) {
            // 从缓存中查询数据
            // pageResponse = queryDataByPageRedis(cfgDB, cfgExtsql, conQuery, pageNO, pageSize, refCache);

            // if (pageResponse.getTotal() != null && pageResponse.getTotal() > 0) {

            //     // 设置默认值
            //     pageNO = pageNO.equals("0") ? "1" : pageNO;
            //     pageSize = pageSize.equals("0") ? "1" : pageSize;

            //     // 使用内存分页
            //     int fromIndex = 0, toIndex = 0, recount = pageResponse.getRows().size();
            //     fromIndex = Integer.valueOf(pageSize) * (Integer.valueOf(pageNO) - 1);
            //     toIndex =
            //             fromIndex + Integer.valueOf(pageSize) >= recount
            //                     ? recount
            //                     : fromIndex + Integer.valueOf(pageSize);

            //     pageResponse.setRows(pageResponse.getRows().subList(fromIndex, toIndex));
            // }
        } else {
            pageResponse = queryDataByPage(cfgDB, pageNO, pageSize, passConditionMap, cfgExtsql);
        }
        return pageResponse;
    }

    /**
     * show 从缓存中分页查询数据
     *
     * @param cfgDB
     * @param cfgExtsql
     * @param conQuery
     * @param pageNO
     * @param pageSize
     * @param refCache
     * @return
     */
    private PageResponseVo queryDataByPageRedis(
            CfgDb cfgDB,
            CfgExtsql cfgExtsql,
            String conQuery,
            String pageNO,
            String pageSize,
            String refCache) {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();

        List<Map<String, Object>> responseData = new ArrayList<>();
        Map<String, String> condion = null;
        if (null != conQuery && !conQuery.equals("")) {
            condion = (Map<String, String>) JSON.parse(conQuery);
        }

        // 计算数据缓存页数(缓存默认一页500条记录)
        Integer tatol = Integer.valueOf(pageNO) * Integer.valueOf(pageSize);
        Integer page = (int) Math.ceil(tatol / 500.0);
        String pageNum = page.toString();

        // 获取普通redisKey
        String redisKeystr = redisKey.generateKey(cfgExtsql.getGuid(), condion, "1", "0", pageNum);
        List<Map<String, Object>> redisData = new ArrayList<>();
        setMaplock(redisKeystr);

        if (refCache.equals("true")) {
            // 强制刷新
//      redisCacheService.delObject(redisKeystr, rdi);
            cacheUtil.delCache(redisKeystr);
            redisData = queryDataAddRedis(redisKeystr, cfgDB, cfgExtsql, conQuery, pageNum, "500");
        } else {
//      if (redisCacheService.stringExists(redisKeystr, rdi)) {
//        // 获取普通缓存数据
//        redisData = queryDataBykey(redisKeystr);
//
//        logger.warn("===========Begin脚本服务分页查询==============");
//        logger.warn("服务ID：" + cfgExtsql.getGuid());
//        logger.warn("RedisKey：" + redisKeystr);
//        logger.warn("===========End脚本查询服务=================");
//      } else {
            // 从数据库中查询数据并添加到缓存
            redisData = queryDataAddRedis(redisKeystr, cfgDB, cfgExtsql, conQuery, pageNum, "500");
//      }
        }

        if (redisData != null && redisData.size() > 0) {
            pageResponse.setTotal(redisData.size());
            pageResponse.setRows(redisData);
        }
        return pageResponse;
    }

    /**
     * 从数据库中分页查询数据
     *
     * @param cfgDB
     * @param pageNO
     * @param pageSize
     * @param cfgExtsql
     * @return
     */
    public PageResponseVo<Map<String, Object>>
    queryDataByPage(CfgDb cfgDB,
                    String pageNO, String pageSize,
                    Map<String, Object> passConditionMap,
                    CfgExtsql cfgExtsql) {

        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();

        // 结束条数和起始条数

        String sqlWithCustomToken = processSqlByFreemarker(cfgExtsql.getDtSql(), passConditionMap);


        Map<String, String> fieldJavaType = new HashMap<>();
        List<BaseSqlFieldType> baseSqlFieldTypeList = cfgExtSqlService.findByServiceId(cfgExtsql.getGuid());
        for (BaseSqlFieldType baseSqlFieldType : baseSqlFieldTypeList) {
            fieldJavaType.put(baseSqlFieldType.getFieldName(), baseSqlFieldType.getFieldJavaType());
        }
        // here is null , read default file
        // here is can comment
        // first construct script business preparedStatement
        BusinessScriptPreparedStatement bps
                = BusinessScriptPreparedStatement.builder(cfgDB.getDbVersion())
                .serviceGuid(cfgExtsql.getGuid())
                .originSqlWithCustomToken(sqlWithCustomToken)
                .fieldTypeMap(fieldJavaType)
                .passCondition(passConditionMap)
                .isPageable(XCommonUtils.isPageable(cfgExtsql.getIspagination()))
                .pageNumber(pageNO)
                .filterSqlHandler(ApplicationContextHelper.getApplicationContext().getBean(PrepareFilterSqlHandler.class).handlerParameter())
                .pageSize(pageSize).build();

        // second handle script business statement
        List<Map<String, Object>> rowList = null;
        Integer rowCount = null;

        rowList = businessScriptPreparedStatementHandler.findRowListOfFieldResultByExecuteSql(bps);
        rowCount = businessScriptPreparedStatementHandler.rowCountByExecuteSql(bps);
        // third supply field type map of java type
        // construct sql content
        // try {

        //     if (null != cfgExtsql) {
        //         // 查询总条数
        //         String count_sql = "select count(*) from (" + sqlWithCustomToken + ") a";
        //         // 条件查询语句
        //         String list_sql = "SELECT * FROM (" + sqlWithCustomToken + ") a LIMIT " + startSize + "," + pageSize;
        //         if (null != cfgDB) {
        //             switch (cfgDB.getDbVersion()) {
        //                 case "ORACLE":
        //                     // 查询总条数
        //                     count_sql = "select count(*) from (" + sqlWithCustomToken + ")";
        //                     // 条件分页 查询语句
        //                     list_sql =
        //                             "SELECT * FROM (  SELECT A.*, ROWNUM RN  FROM (SELECT * FROM ("
        //                                     + sqlWithCustomToken
        //                                     + ") ) A WHERE ROWNUM <= "
        //                                     + endSize
        //                                     + " )  WHERE RN > "
        //                                     + startSize;
        //                     break;

        //                 case "MYSQL":
        //                     break;
        //                 case "SQLSERVER":
        //                     String strOrder = "";
        //                     List<BaseSqlconfig> sqlconfigList =
        //                             extBaseSqlconfigMapper.selectSqlByServiceId(cfgExtsql.getGuid());
        //                     strOrder = setOrdersql(sqlconfigList, sqlWithCustomToken);

        //                     if (strOrder == null || strOrder.equals("")) {
        //                         strOrder = "order by getdate()";
        //                     }

        //                     count_sql = "select count(*) from (" + sqlWithCustomToken + ") a";

        //                     list_sql =
        //                             "select * from(select ROW_NUMBER() OVER ("
        //                                     + strOrder
        //                                     + " ) RN, AA.*  from ("
        //                                     + sqlWithCustomToken
        //                                     + ")AA )a WHERE RN Between "
        //                                     + (startSize + 1)
        //                                     + " and "
        //                                     + endSize;
        //                     break;
        //                 case "KingbaseV8":
        //                     break;
        //             }

        //             logger.warn("===========Begin脚本服务分页查询==============");
        //             logger.warn("服务ID：" + cfgExtsql.getGuid());
        //             logger.warn("执行查询sql脚本：" + list_sql);
        //             logger.warn("执行总条数sql脚本：" + count_sql);
        //             logger.warn("===========End脚本服务查询==============");

        //             if (cfgDB.getDbVersion().equals("MongoDB")) {
        //                 MongoDBUtil instance = MongoDBUtil.getInstance();
        //                 QueryInfo queryInfo = instance.sql2MongoQuery(sqlWithCustomToken);
        //                 queryInfo.setDb(instance.createConnection(cfgDB.getDbUrl()));
        //                 list = instance.find(queryInfo, startSize, endSize);
        //                 count = instance.findCount(queryInfo);
        //                 instance.close();
        //             } else {
        //                 count = multipleTableServerMapper.multipleTBCount(count_sql);
        //                 list = multipleTableServerMapper.queryMultipleTBList(list_sql);
        //             }

        //             // 取出服务中的clob字段和blob字段
        //             List<String> clobFList = new ArrayList<String>();
        //             List<String> blobFList = new ArrayList<String>();
        //             List<BaseSqlconfig> fieldList =
        //                     extBaseSqlconfigMapper.selectSqlConfigByServiceId(cfgExtsql.getGuid());
        //             if (null != fieldList && fieldList.size() > 0) {
        //                 for (BaseSqlconfig baseSqlconfig : fieldList) {
        //                     if (null != baseSqlconfig.getRemark() && baseSqlconfig.getRemark().equals("clob")) {
        //                         clobFList.add(baseSqlconfig.getFiledname());
        //                     } else if (null != baseSqlconfig.getRemark()
        //                             && baseSqlconfig.getRemark().equals("blob")) {
        //                         blobFList.add(baseSqlconfig.getFiledname());
        //                     }
        //                 }
        //             }
        //             // 处理clob类型和blob类型
        //             if (null != clobFList && clobFList.size() > 0) {
        //                 for (String clobStr : clobFList) {
        //                     for (Map<String, Object> map : list) {
        //                         if (map.get(clobStr) != null && !"".equals(map.get(clobStr))) {
        //                             String clobToString = ClobToString((CLOB) map.get(clobStr));
        //                             map.put(clobStr, clobToString);
        //                         }
        //                     }
        //                 }
        //             } else if (null != blobFList && blobFList.size() > 0) {
        //                 for (String blobStr : blobFList) {
        //                     for (Map<String, Object> map : list) {
        //                         if (map.get(blobStr) != null && !"".equals(map.get(blobStr))) {
        //                             String clobToString = BlobToString((BLOB) map.get(blobStr));
        //                             map.put(blobStr, clobToString);
        //                         }
        //                     }
        //                 }
        //             }

        //                        }
        //     }
        // } catch (Exception e) {
        //     throw new BaseException(e, getSysException());
        // }

        pageResponse.setTotal(rowCount.intValue());
        pageResponse.setRows(rowList);

        return pageResponse;
    }

    /**
     * show 设置锁对象 create by guoyanfei on 2018/12/28
     */
    private void setMaplock(String redisKeystr) {
        if (maplock != null && maplock.size() > 0) {
            boolean flag = maplock.containsKey(redisKeystr);
            if (!flag) {
                // 不存在
                maplock.put(redisKeystr, redisKeystr);
            }
        } else {
            // 不存在
            maplock.put(redisKeystr, redisKeystr);
        }
    }

    /**
     * show 从数据库中分页查询数据并添加到缓存
     *
     * @param redisKeystr
     * @param cfgDB
     * @param cfgExtsql
     * @param conQuery
     * @param pageNum
     * @param pageSize
     * @return
     */
    private List<Map<String, Object>> queryDataAddRedis(
            String redisKeystr,
            CfgDb cfgDB,
            CfgExtsql cfgExtsql,
            String conQuery,
            String pageNum,
            String pageSize) {
        List<Map<String, Object>> redisData = new ArrayList<>();
        synchronized (maplock.get(redisKeystr)) {
//      if (redisCacheService.stringExists(redisKeystr, rdi)) {
//        // 获取普通缓存数据
//        redisData = queryDataBykey(redisKeystr);
//      } else {
//        PageResponseVo<Map<String, Object>> pageResponse =
//            queryDataByPage(cfgDB, pageNum, "500", conQuery, cfgExtsql);
//        List<Map<String, Object>> viewData = pageResponse.getRows();
//        String extendStr = JSON.toJSONStringWithDateFormat(viewData, dateformat);
//
//        // 过期时间分钟转换秒
//        long timeOut = Long.valueOf(cfgExtsql.getCatchtime()) * 60;
//        redisCacheService.saveObjectTimeOut(redisKeystr, extendStr, rdi, timeOut);
//
//        // 从缓存中查询数据
//        redisData = queryDataBykey(redisKeystr);
//      }
            redisData = cacheServiceData.queryDataAddRedisMulti(redisKeystr, cfgDB, cfgExtsql, conQuery, pageNum, "500");
        }
        return redisData;
    }

    /**
     * show 通过redisKey查询数据 create by guoyanfei on 2018/12/20
     *
     * @param redisKey
     * @return
     */
//    private List<Map<String, Object>> queryDataBykey(String redisKey) {
//        List<Map<String, Object>> redisData = new ArrayList<>();
//        String cfgStr = redisCacheService.getObject(redisKey, rdi);
//        List<Object> dataobject = JSON.parseArray(cfgStr);
//        if (dataobject != null && dataobject.size() > 0) {
//            for (Object object : dataobject) {
//                Map<String, Object> ret = (Map<String, Object>) object;
//                redisData.add(ret);
//            }
//        }
//        return redisData;
//    }

    /**
     * 不分页获取单表查询数据
     *
     * @param cfgDB
     * @param conQuery
     * @param cfgExtsql // * @param fieldString
     * @return
     * @throws Exception
     */
    @Override
    public PageResponseVo multipleTableServerQuery(
            CfgDb cfgDB, String conQuery, CfgExtsql cfgExtsql, String refCache) throws Exception {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();

        if (cfgExtsql.getSyscatch().equals(1)) {
            // 缓存中查询数据
            pageResponse = multipleTableNoPageRedis(cfgDB, cfgExtsql, conQuery, refCache);
        } else if (cfgExtsql.getSyscatch().equals(0)) {
            // 从数据库中查询数据
            pageResponse = multipleTableQuery(cfgDB, conQuery, cfgExtsql);
        }

        return pageResponse;
    }

    /**
     * 脚本服务不分页查询，从缓存中查询数据 created by guoyanfei on 2019/07/12
     *
     * @param cfgDb     数据源实体
     * @param cfgExtsql 脚本服务实体
     * @param conQuery  查询条件
     * @param refCache  (是否强制刷新缓存)
     * @return
     */
    private PageResponseVo multipleTableNoPageRedis(
            CfgDb cfgDb, CfgExtsql cfgExtsql, String conQuery, String refCache) {
        PageResponseVo pageResponse = new PageResponseVo();
        Map<String, String> condion = null;
        if (null != conQuery && !conQuery.equals("")) {
            condion = (Map<String, String>) JSON.parse(conQuery);
        }

        // 获取普通redisKey
        String redisKeystr = redisKey.generateKey(cfgExtsql.getGuid(), condion, "1", "0", "0");
        setMaplock(redisKeystr);
//        if (redisCacheService.stringExists(redisKeystr, rdi)) {
//            if (refCache.equals("false")) {
//                // 从缓存中读取数据
//                List<Map<String, Object>> redisData = queryDataBykey(redisKeystr);
//                if (redisData != null && redisData.size() > 0) {
//                    pageResponse.setRows(redisData);
//                    pageResponse.setTotal(redisData.size());
//                }
//
//                logger.warn("===========Begin脚本服务不分页查询==============");
//                logger.warn("服务ID：" + cfgExtsql.getGuid());
//                logger.warn("RedisKey：" + redisKeystr);
//                logger.warn("===========End脚本查询服务=================");
//            } else if (refCache.equals("true")) {
//                // 强制刷新数据
//                redisCacheService.delObject(redisKeystr, rdi);
//                // 查询数据并更新缓存
//                pageResponse = multipleTableAddRedis(redisKeystr, cfgDb, cfgExtsql, conQuery);
//            }
//
//        } else {
        // 缓存失效，查询数据并更新缓存
        pageResponse = multipleTableAddRedis(redisKeystr, cfgDb, cfgExtsql, conQuery);
//        }

        return pageResponse;
    }

    /**
     * 脚本不分页查询数据并添加到缓存中 created by guoyanfei on 2019/07/12
     *
     * @param redisKeystr rediskey
     * @param cfgDb       数据库实体
     * @param cfgExtsql   脚本服务实体
     * @param conQuery    查询条件
     * @return
     */
    private synchronized PageResponseVo multipleTableAddRedis(
            String redisKeystr, CfgDb cfgDb, CfgExtsql cfgExtsql, String conQuery) {
        PageResponseVo pageResponse = new PageResponseVo();
        synchronized (maplock.get(redisKeystr)) {
//            if (redisCacheService.stringExists(redisKeystr, rdi)) {
//                // 从缓存中读取数据
//                List<Map<String, Object>> redisData = queryDataBykey(redisKeystr);
//                if (redisData != null && redisData.size() > 0) {
//                    pageResponse.setRows(redisData);
//                    pageResponse.setTotal(redisData.size());
//                }
//            } else {
//                // 从数据库中查询数据
            pageResponse = multipleTableQuery(cfgDb, conQuery, cfgExtsql);
//                // 将查询后的数据存放至缓存
//                String extendStr = JSON.toJSONStringWithDateFormat(pageResponse.getRows(), dateformat);
//                // 过期时间分钟转换秒
//                long timeOut = Long.valueOf(cfgExtsql.getCatchtime()) * 60;
//                redisCacheService.saveObjectTimeOut(redisKeystr, extendStr, rdi, timeOut);
//            }
            pageResponse = cacheServiceData.multipleTableAddRedis(redisKeystr, cfgDb, cfgExtsql, conQuery);
        }
        return pageResponse;
    }

    /**
     * 从数据库中获取获取单表查询数据
     *
     * @param cfgDB
     * @param conQuery
     * @param cfgExtsql // * @param fieldString
     * @return
     * @throws Exception modify by guoyanfei on 2019/07/11 修改脚本服务不分页查询
     */
    public PageResponseVo multipleTableQuery(CfgDb cfgDB, String conQuery, CfgExtsql cfgExtsql) {
        // conQuery
        PageResponseVo pageResponse = new PageResponseVo();
        List<Map<String, Object>> list = new ArrayList<>(); // 结果
        Map pays = null; // 条件
        Long count = 0L; // 总条数
        String sql = ""; // 查询结果sql
        String count_sql = ""; // 查询总条数sql
        try {
            // 参数处理
            if (null != conQuery && !conQuery.equals("")) {
                pays = (Map) JSON.parse(conQuery);
            }

            // sql语句处理
            sql = processSqlByFreemarker(cfgExtsql.getDtSql(), pays);
            List<BaseSqlconfig> sqlconfigList =
                    extBaseSqlconfigMapper.selectSqlByServiceId(cfgExtsql.getGuid());
            String strorder = setOrdersql(sqlconfigList, sql);
            sql += strorder;
            if (cfgDB.getDbVersion().equals("SQLSERVER")) {
                String sqlserver_sql = sql;
                if (sqlserver_sql.toLowerCase().indexOf("order by") != -1) {
                    sqlserver_sql =
                            "SELECT TOP 100 percent getdate() eltime "
                                    + sql.substring(sql.toLowerCase().indexOf("from"));
                }
                count_sql = "select count(*) from (" + sqlserver_sql + ") a";
            } else {
                count_sql = "select count(*) from (" + sql + ") a";
            }

            logger.warn("===========Begin脚本服务不分页查询==============");
            logger.warn("服务ID：" + cfgExtsql.getGuid());
            logger.warn("执行查询sql脚本：" + sql);
            logger.warn("执行总条数sql脚本：" + count_sql);
            logger.warn("===========End脚本服务查询==============");

            if (cfgDB.getDbVersion().equals("MongoDB")) {
                MongoDBUtil instance = MongoDBUtil.getInstance();
                QueryInfo queryInfo = instance.sql2MongoQuery(sql);
                queryInfo.setDb(instance.createConnection(cfgDB.getDbUrl()));
                list = instance.findAll(queryInfo);
                instance.close();
            } else {
                //        count = multipleTableServerMapper.multipleTBCount(count_sql);
                list = multipleTableServerMapper.queryMultipleTBList(sql);
            }
            // 获取查询总数
            if (list != null) {
                count = (long) list.size();
            }

            // 取出服务中的clob字段和blob字段
            List<String> clobFList = new ArrayList<String>();
            List<String> blobFList = new ArrayList<String>();
            List<BaseSqlconfig> fieldList =
                    extBaseSqlconfigMapper.selectSqlConfigByServiceId(cfgExtsql.getGuid());
            if (null != fieldList && fieldList.size() > 0) {
                for (BaseSqlconfig baseSqlconfig : fieldList) {
                    if (null != baseSqlconfig.getRemark() && baseSqlconfig.getRemark().equals("clob")) {
                        clobFList.add(baseSqlconfig.getFiledname());
                    } else if (null != baseSqlconfig.getRemark()
                            && baseSqlconfig.getRemark().equals("blob")) {
                        blobFList.add(baseSqlconfig.getFiledname());
                    }
                }
            }
            // 处理clob类型和blob类型
            if (null != clobFList && clobFList.size() > 0) {
                for (String clobStr : clobFList) {
                    for (Map<String, Object> map : list) {
                        if (map.get(clobStr) != null && !"".equals(map.get(clobStr))) {
                            String clobToString = ClobToString((CLOB) map.get(clobStr));
                            map.put(clobStr, clobToString);
                        }
                    }
                }
            } else if (null != blobFList && blobFList.size() > 0) {
                for (String blobStr : blobFList) {
                    for (Map<String, Object> map : list) {
                        if (map.get(blobStr) != null && !"".equals(map.get(blobStr))) {
                            String clobToString = BlobToString((BLOB) map.get(blobStr));
                            map.put(blobStr, clobToString);
                        }
                    }
                }
            }

        } catch (Exception e) {
            throw new BaseException(e, getSysException());
        }

        pageResponse.setTotal(count.intValue());
        pageResponse.setRows(list);

        return pageResponse;
    }

    /**
     * 设置排序拼接sql排序部分 create by guoyanfei on 2018/07/10
     *
     * @param sqlconfigList 排序集合
     * @param sql           执行的sql
     * @return 包含排序sql
     */
    private String setOrdersql(List<BaseSqlconfig> sqlconfigList, String sql) {
        String strorder = "";
        if (sqlconfigList.size() > 0 && sql.toLowerCase().indexOf("select") != -1) {
            strorder += " " + " order by" + " ";
            int i = 0;
            for (BaseSqlconfig baseSqlconfig : sqlconfigList) {
                if (baseSqlconfig.getQueryorder() == 1) {
                    if (i == 0) {
                        strorder += baseSqlconfig.getFiledname() + " asc";
                    } else {
                        strorder += "," + baseSqlconfig.getFiledname() + " asc";
                    }
                } else {
                    if (i == 0) {
                        strorder += baseSqlconfig.getFiledname() + " desc";
                    } else {
                        strorder += "," + baseSqlconfig.getFiledname() + " desc";
                    }
                }
                i++;
            }
        }

        return strorder;
    }

    /**
     * <pre>
     * processSqlByFreemarker(freemarker处理sql)
     * &#64;author：FanWenquan
     * &#64;date：2017年8月31日 上午10:34:17
     * &#64;param sql
     * &#64;param root
     * &#64;return
     * </pre>
     */
    private String processSqlByFreemarker(String sql, Map root) {
        String str = "";
        Configuration cfg = new Configuration();
        StringTemplateLoader stringLoader = new StringTemplateLoader();
        if (XCommonUtils.isEmpty(sql))
            return str;

        String sqlWithoutHandlePlaceHolderValue = sql.replaceAll(XCommonUtils.OpenTokenInFreemarker, XCommonUtils.OpenToken).replaceAll(XCommonUtils.CloseTokenInFreemarker, XCommonUtils.CloseToken);

        try {
            stringLoader.putTemplate("myTemplate", sqlWithoutHandlePlaceHolderValue);
            cfg.setTemplateLoader(stringLoader);

            Template template = cfg.getTemplate("myTemplate", "utf-8");
            StringWriter writer = new StringWriter();
            template.process(root, writer);
            str = writer.toString();
            // System.out.println(writer.toString());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (TemplateException e) {
            e.printStackTrace();
        }
        return str;
    }

    @Override
    public List<Map> queryTest(String sql) {
        List<Map> list = multipleTableServerMapper.queryMultipleTBListTH(sql);
        return list;
    }

    /**
     * 使用OGNL进行分页查询
     *
     * @param cfgDB            数据库配置信息
     * @param pageNO           页码
     * @param pageSize         页大小
     * @param passConditionMap 查询条件映射
     * @param cfgExtsql        扩展SQL配置
     * @param queryFileds      查询字段信息列表
     * @param paramType
     * @return 分页查询结果，包括总记录数和数据行
     */
    @Override
    public PageResponseVo<Map<String, Object>> ognlQueryPagination(CfgDb cfgDB, String pageNO, String pageSize,
                                                                   Map<String, Object> passConditionMap,
                                                                   CfgExtsql cfgExtsql, List<QueryFiled> queryFileds,
                                                                   Map<String, String> paramType) {
        // 初始化分页响应对象和数据存储Map
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<>();
        // 开始MyBatis分页
        PageHelper.startPage(Integer.parseInt(pageNO), Integer.parseInt(pageSize));
        //过滤有效参数
        Map<String, Object> map = new HashMap<>(passConditionMap);
        for (QueryFiled queryFiled : queryFileds) {
            String parametercode = queryFiled.getParametercode();
            handParamType(passConditionMap, paramType, parametercode, map);
        }
        //过滤器处理
        dealSelfFilter(cfgExtsql);

        // 执行查询并获取结果行
        List<Map<String, Object>> resultRowsMybatis = getResultRowsMybatis(map, cfgExtsql);
        // 创建并填充分页信息对象
        PageInfo<Map<String, Object>> info = new PageInfo<>(resultRowsMybatis);

        // 获取总记录数
        long total = info.getTotal();
        // 设置分页响应对象的总记录数和数据行
        pageResponse.setTotal((int) total);
        pageResponse.setRows(resultRowsMybatis);
        // 返回分页查询结果
        return pageResponse;
    }

    public void dealSelfFilter(CfgExtsql cfgExtsql) {
        FilterSqlHandlerDTO filterSqlHandlerDTO = ApplicationContextHelper.getApplicationContext().getBean(PrepareFilterSqlHandler.class).handlerParameter();
        if (filterSqlHandlerDTO!=null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date startTime = filterSqlHandlerDTO.getFilterStartTime();
            Date endTime = filterSqlHandlerDTO.getFilterEnterTime();
            String startT = null;
            String endT = null;
            if (startTime != null) {
                startT = sdf.format(startTime);
            }
            if (endTime != null) {
                endT = sdf.format(endTime);
            }
            String dtSql = cfgExtsql.getDtSql();
            List<AcServiceFilterField> filterFiledList = filterSqlHandlerDTO.getFilterFiledList();
            String sqlNew = "select * from (" + dtSql + ") as tmp  ";
            String where = "where 1=1";
            for (AcServiceFilterField acServiceFilterField : filterFiledList) {
                String filterType = acServiceFilterField.getFilterType();
                String fieldCode = acServiceFilterField.getFieldCode();
                if ("TM".equals(filterType)) {
                    if (startT != null && !startT.isEmpty()) {
                        where += " and tmp." + fieldCode + " >= '" + startT + "' ";
                    }
                    if (endT != null && !endT.isEmpty()) {
                        where += " and tmp." + fieldCode + " <![CDATA[ <= ]]> '" + endT + "'";
                    }
                }
                if ("STCD".equals(filterType)) {
                    if (!fieldCode.isEmpty()){
                        sqlNew += " INNER JOIN ac_filter_station as afs ON tmp." + fieldCode + " = afs.stcd ";
                    }
                }
            }
            cfgExtsql.setDtSql(sqlNew + where);
        }

    }

    private static void handParamType(Map<String, Object> passConditionMap, Map<String, String> paramType, String parametercode, Map<String, Object> map) {
        String pType = paramType.get(parametercode);
        Object s = passConditionMap.get(parametercode);
        if (null != s) {
            if (pType.contains("String[]")) {
                List<String> list = new ArrayList<>();
                list.addAll(Arrays.asList(s.toString().split(",")));
                map.put(parametercode, list);
            }
            if (pType.contains("Integer")) {
                map.put(parametercode, ObjectUtils.isEmpty(s) ? null : Integer.valueOf(s.toString()));
            }
            if (pType.contains("Integer[]")) {
                List<Integer> list = new ArrayList<>();
                for (String string : s.toString().split(",")) {
                    list.add(Integer.parseInt(string));
                }
                map.put(parametercode, list);
            }
            if (pType.contains("Double")) {
                map.put(parametercode, ObjectUtils.isEmpty(s) ? null : Double.valueOf(s.toString()));
            }
            if (pType.contains("Double[]")) {
                List<Double> list = new ArrayList<>();
                for (String string : s.toString().split(",")) {
                    list.add(Double.parseDouble(string));
                }
                map.put(parametercode, list);
            }
            if (pType.contains("Float")) {
                map.put(parametercode, ObjectUtils.isEmpty(s) ? null : Float.valueOf(s.toString()));
            }
            if (pType.contains("Float[]")) {
                List<Float> list = new ArrayList<>();
                for (String string : s.toString().split(",")) {
                    list.add(Float.parseFloat(string));
                }
                map.put(parametercode, list);
            }
            if (pType.contains("Long")) {
                map.put(parametercode, ObjectUtils.isEmpty(s) ? null : Long.valueOf(s.toString()));
            }
            if (pType.contains("Long[]")) {
                List<Long> list = new ArrayList<>();
                for (String string : s.toString().split(",")) {
                    list.add(Long.parseLong(string));
                }
                map.put(parametercode, list);
            }

        }
    }


    /**
     * 使用MyBatis执行动态SQL查询，并返回查询结果
     *
     * @param map       一个包含查询参数的Map对象
     * @param cfgExtsql CfgExtsql对象，包含SQL模板和配置信息
     * @return 返回一个包含查询结果的List<Map < String, Object>>对象
     */
    public List<Map<String, Object>> getResultRowsMybatis(Map<String, Object> map, CfgExtsql cfgExtsql) {
        String serviceId = cfgExtsql.getGuid();
        // 动态生成映射器ID
        String statementId = serviceId + ".dynamic";

        try (SqlSession session = sqlSessionFactory.openSession(true)) {
            //如果服务不存在，初始化一个新对象
            if (!serviceConcurrentMap.containsKey(serviceId)) {
                synchronized (serviceConcurrentMap) {
                    if (!serviceConcurrentMap.containsKey(serviceId)) {
                        serviceConcurrentMap.put(serviceId, new XMLMapperBuilderHelper());
                    }
                }
            }
            //1.初始化XML
            XMLMapperBuilderHelper initXmlUtil = serviceConcurrentMap.get(serviceId);
            initXmlUtil.loadXmlConfig(session.getConfiguration(), cfgExtsql);
            //2.执行对应的XML
            return session.selectList(statementId, map);

        } catch (Exception e) {
            logger.error("Error occurred during query execution: {}", e.getMessage(), e);
            throw new RuntimeException("Error occurred during query execution: " + e.getMessage(), e);
        }
    }

    public static Object convertObject(Object obj, String javaType) {
        if (obj == null) {
            return null;
        }
        String objStr = obj.toString();
        if (objStr.isEmpty()) {
            return null;
        }
        return obj.toString();
    }


    private String wrapSql2Xml(String sql, String nameSpace) {
        String xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n"
                + "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">"
                + "<mapper namespace=\"" + nameSpace + "\">"
                + "  <select id=\"dynamic\" resultType='java.util.Map'>"
                + sql
                + "  </select>"
                + "</mapper>";
        return xml;
    }

    // 将字CLOB转成STRING类型
    private String ClobToString(CLOB clob) throws SQLException, IOException {
        // nclob
        String reString = "";
        Reader is = clob.getCharacterStream(); // 得到流
        BufferedReader br = new BufferedReader(is);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (s != null) { // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(s);
            s = br.readLine();
        }
        reString = sb.toString();
        br.close();
        return reString;
    }

    private String BlobToString(BLOB blob) throws SQLException, Exception {
        String result = "";
        try {
            if (blob != null) {
                StringBuffer buffer = new StringBuffer();
                InputStream is = null;
                is = blob.getBinaryStream();
                InputStreamReader isr = new InputStreamReader(is);
                if (isr.ready()) {
                    Reader reader = new BufferedReader(isr);
                    int ch;
                    while ((ch = reader.read()) > -1) {
                        buffer.append((char) ch);
                    }
                }
                isr.close();
                is.close();
                result = buffer.toString();
            }
        } catch (Exception e) {
            System.err.println("error : " + e.getMessage());
            // throw e;
            return result;
        }
        return result;
    }

    public Map<String, SysException> getSysException() {
        List<Map<String, Object>> lists = null;
        Map<String, SysException> map = new HashMap<>();
        List<SysException> list = extSysExceptionMapper.selectAllList();
        for (SysException sysException : list) {
            map.put(sysException.getExceptionName(), sysException);
        }
        return map;
    }
}
